import pymysql
import pandas as pd
import time

# -------------------------------- DB basic info------------------------------
mysql_ip = '172.16.122.68'
mysql_port = 3306
mysql_user = 'test'
mysql_password = 'dpi@2021'
mysql_database = 'zentao'

# mysql_ip = '127.0.0.1'
# mysql_port = 3306
# mysql_user = 'root'
# mysql_password = 'root'
# mysql_database = 'zentao'


# ----------------------------------------------------------------------------

pyconect = pymysql.connect(
    host=mysql_ip,
    port=mysql_port,
    user=mysql_user,
    password=mysql_password,
    db=mysql_database,
    charset='utf8'
)

pycursor = pyconect.cursor(pymysql.cursors.DictCursor)

sql = """
select 
			pj.id as "项目编号",
			pj.name as "项目名",
			pd.id as "产品/子系统编号",
			pd.`name` as "产品/子系统名称",
			sty.id 										as "ID", 
			sty.title 								as "研发需求名称",
			sty.pri 									as "P",
			pplan.title 							as "计划",
			sty.`status`							as "状态",
			usr.realname							as "创建者",
			sty.estimate							as "预计",
			sty.reviewedBy						as "评审者",
			sty.reviewedDate 					as "评审时间",
			sty.stage 								as "阶段",
			if(usrB.realname is Null, "关闭", usrB.realname) 						as "指派给",
			sty.assignedDate 					as "指派日期",
			sty.keywords							as "关键词",
			sty.source								as "来源",
			sty.sourceNote						as "来源备注",
			sty.type									as "类别",
			sty.openedDate						as "创建日期",
			sty.closedReason					as "关闭原因",
			usrC.realname							as "最后修改",
			sty.lastEditedDate				as "最后修改日期",
			sty.activatedDate 				as "激活日期",
			sty.feedbackBy 						as "反馈者",
			sty.notifyEmail 					as "通知邮箱",
			sty.mailto 								as "抄送给",
			sty.version 							as "版本号",
			count(DISTINCT tsk.id) 		as "T",
			0																		as "B",
			count(DISTINCT cas.id)		as "C",
			sty.subStatus 						as "子状态"
			

FROM
		zt_story as sty 
LEFT JOIN
		zt_product as pd 
ON
		pd.id = sty.product
LEFT JOIN
		zt_project as pj 
ON
		pj.id = pd.program
LEFT JOIN
		zt_productplan as pplan 
ON
		pplan.id = sty.plan
LEFT JOIN
		zt_task as tsk 
ON
		tsk.story = sty.id
LEFT JOIN
		zt_case as cas 
ON
		cas.story = sty.id
LEFT JOIN
		zt_user as usr 
ON
		usr.account=sty.openedBy
LEFT JOIN
		zt_user as usrB 
ON
		usrB.account=sty.assignedTo		
LEFT JOIN
		zt_user as usrC 
ON
		usrC.account=sty.lastEditedBy				
	
WHERE
		sty.deleted = '0'
AND
		pj.deleted = '0'
AND
		pd.deleted = '0'
GROUP BY
		sty.id
"""


pycursor.execute(sql)
result = pycursor.fetchall()

df = pd.DataFrame.from_dict(result)

df.to_excel(f"project_story_all_basic_data_{'_'.join(str(item) for item in tuple(time.localtime())[:-3])}.xlsx", sheet_name='project数据', index=True, index_label="序号")

